//package cn.haohaowo.spring.chapter7;
//
//import java.sql.CallableStatement;
//import java.sql.Connection;
//import java.sql.PreparedStatement;
//import java.sql.ResultSet;
//import java.sql.SQLException;
//import java.sql.Types;
//import java.util.ArrayList;
//import java.util.HashMap;
//import java.util.List;
//import java.util.Map;
//
//import org.junit.After;
//import org.junit.Assert;
//import org.junit.Before;
//import org.junit.BeforeClass;
//import org.junit.Test;
//import org.springframework.dao.DataAccessException;
//import org.springframework.jdbc.core.CallableStatementCreator;
//import org.springframework.jdbc.core.JdbcTemplate;
//import org.springframework.jdbc.core.PreparedStatementCallback;
//import org.springframework.jdbc.core.PreparedStatementCreator;
//import org.springframework.jdbc.core.PreparedStatementSetter;
//import org.springframework.jdbc.core.ResultSetExtractor;
//import org.springframework.jdbc.core.RowCallbackHandler;
//import org.springframework.jdbc.core.RowMapper;
//import org.springframework.jdbc.core.SqlInOutParameter;
//import org.springframework.jdbc.core.SqlOutParameter;
//import org.springframework.jdbc.core.SqlParameter;
//import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
//import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
//import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
//import org.springframework.jdbc.core.namedparam.SqlParameterSource;
//import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
//import org.springframework.jdbc.datasource.DriverManagerDataSource;
//import org.springframework.jdbc.support.rowset.SqlRowSet;
//
//
//public class JdbcTemplateTest {
//
//	private static JdbcTemplate jdbcTemplate;
//	
//	@BeforeClass
//	public static void setUpClass() {
//		String url = "jdbc:mysql://localhost:3306/haohaowo?useUnicode=true&characterEncoding=utf-8";
//		String username = "root";
//		String password = "123456";
//		String driver = "com.mysql.jdbc.Driver";
//		
////		String url = "jdbc:hsqldb:mem:test";
////		String username = "sa";
////		String password = "";
////		String driver = "org.hsqldb.jdbcDriver";
//		
//		DriverManagerDataSource dataSource = new DriverManagerDataSource(url, username, password);
//		dataSource.setDriverClassName(driver);
//		jdbcTemplate = new JdbcTemplate(dataSource);
//	}
//	
////	@Test
//	public void test() {
//		//register sql
//		String sql = " select * from INFORMATION_SCHEMA.SYSTEM_TABLES ";
//		jdbcTemplate.query(sql, new RowCallbackHandler() {
//			@Override
//			public void processRow(ResultSet rs) throws SQLException {
//				//result
//				String value = rs.getString("TABLE_NAME");
//				System.out.println("column TABLE_NAME:" + value);
//			}
//		});
//	}
//	
//	@Before
//	public void setUp() {
//		String createTableSql = "create table if not exists test " +
//				"(id int(10) primary key auto_increment, " +
//				"name varchar(20))";
////		String createTableSql = " create memory table test (id int generated by " +
////				" default as identity primary key, name varchar(100)) ";
//		jdbcTemplate.update(createTableSql);
//		
////		String createHsqldbFunctionSql = " create function function_test(str char(100)) " +
////				" returns int begin return length(str);end ";
////		jdbcTemplate.update(createHsqldbFunctionSql);
//		
//		String createHsqldbProcedureSql = " create procedure procedure_test" +
//				"(inout inoutname varchar(100), out outid int) " +
//				" begin " +
//				"	insert into test(name) values(inoutname); " +
//				"	select id into outid from test; " +
//				"	set inoutname = concat('hello,', inoutname); " +
//				"end";
//		jdbcTemplate.execute(createHsqldbProcedureSql);
////	}
//	
//	@After
//	public void tearDown() {
////		jdbcTemplate.execute("drop function function_test");
//		jdbcTemplate.execute("drop procedure procedure_test");
//		
//		String dropTableSql = "drop table test";
//		jdbcTemplate.execute(dropTableSql);
//	}
//	
////	@Test
//	public void testCURD() {
//		insert();
//		delete();
//		update();
//		select();
//	}
//	
//	private void select() {
//		jdbcTemplate.query("select * from test", new RowCallbackHandler() {
//			@Override
//			public void processRow(ResultSet rs) throws SQLException {
//				System.out.println("id:" + rs.getInt("id"));
//				System.out.println("name:" + rs.getString("name"));
//			}
//		});
//	}
//
//	private void update() {
//		jdbcTemplate.update("update test set name = 'name3' where name = ?", new Object[]{"name1"});
//	}
//
//	private void delete() {
//		jdbcTemplate.update("delete from test where name = ?", new Object[]{"name2"});
//		Assert.assertEquals(1,jdbcTemplate.queryForInt("select count(*) from test"));
//	}
//
//	private void insert() {
//		jdbcTemplate.update("insert into test(name) values('name1')");
//		jdbcTemplate.update("insert into test(name) values('name2')");
//		Assert.assertEquals(2,jdbcTemplate.queryForInt("select count(*) from test"));
//	}
//	
////	@Test
//	public void testPreparedStatement1() {
//		int count = jdbcTemplate.execute(new PreparedStatementCreator() {
//			
//			@Override
//			public PreparedStatement createPreparedStatement(Connection con)
//					throws SQLException {
//				return con.prepareStatement("select count(*) from test");
//			}
//		}, new PreparedStatementCallback<Integer>() {
//			@Override
//			public Integer doInPreparedStatement(PreparedStatement pstmt)
//					throws SQLException, DataAccessException {
//				pstmt.execute();
//				ResultSet rs = pstmt.getResultSet();
//				rs.next();
//				return rs.getInt(1);
//			}
//		});
//		Assert.assertEquals(1, count);
//	}
//	
////	@Test
//	public void testPreparedStatement2() {
//		String insertSql = "insert into test(name) values(?)";
//		int count = jdbcTemplate.update(insertSql, new PreparedStatementSetter() {
//			@Override
//			public void setValues(PreparedStatement pstmt) throws SQLException {
//				pstmt.setObject(1, "name4");
//			}
//		});
//		
//		Assert.assertEquals(1, count);
//		String deleteSql = "delete from test where name = ?";
//		count = jdbcTemplate.update(deleteSql, new Object[]{"name4"});
//		Assert.assertEquals(1, count);
//	}
//	
////	@Test
//	public void testResultSet1() {
//		jdbcTemplate.update("insert into test(name) values('name5')");
//		String listSql = "select * from test";
//		List result = jdbcTemplate.query(listSql, new RowMapper<Map>() {
//			@Override
//			public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
//				Map row = new HashMap();
//				row.put(rs.getInt("id"), rs.getString("name"));
//				return row;
//			}
//		});
//		
//		Assert.assertEquals(1, result.size());
//		jdbcTemplate.update("delete from test where name = 'name5'");
//	}
//	
////	@Test
//	public void testResultSet2() {
//		jdbcTemplate.update("insert into test(name) values('name5')");
//		String listSql = "select * from test";
//		final List result = new ArrayList();
//		jdbcTemplate.query(listSql, new RowCallbackHandler() {
//			@Override
//			public void processRow(ResultSet rs) throws SQLException {
//				Map row = new HashMap();
//				row.put(rs.getInt("id"), rs.getString("name"));
//				result.add(row);
//			}
//		});
//		
//		Assert.assertEquals(1, result.size());
//		jdbcTemplate.update("delete from test where name = 'name5'");
//	}
//	
////	@Test
//	public void testResultSet3() {
//		jdbcTemplate.update("insert into test(name) values('name5')");
//		String listSql = "select * from test";
//		List result = jdbcTemplate.query(listSql, new ResultSetExtractor<List>() {
//			@Override
//			public List extractData(ResultSet rs) throws SQLException,
//					DataAccessException {
//				List result = new ArrayList();
//				while(rs.next()) {
//					Map row = new HashMap();
//					row.put(rs.getInt("id"), rs.getString("name"));
//					result.add(row);
//				}
//				return result;
//			}
//		});
//		
//		Assert.assertEquals(0, result.size());
//		jdbcTemplate.update("delete from test where name = 'name5'");
//	}
//	
////	@Test
//	public void testSelectMethod() {
//		jdbcTemplate.update("insert into test(name) values('name5')");
//		
//		int count = jdbcTemplate.queryForInt("select count(*) from test");
//		Assert.assertEquals(1, count);
//		
//		Map map = jdbcTemplate.queryForMap("select * from test where name = 'name5'");
//		Assert.assertEquals(2, map.size());
//		
//		count = jdbcTemplate.queryForObject("select count(*) from test", Integer.class);
//		Assert.assertEquals(1, count);
//		
//		List list = jdbcTemplate.queryForList("select * from test");
//		Assert.assertEquals(1, list.size());
//		
//		list = jdbcTemplate.queryForList("select name from test where name = ?",
//				new Object[]{"name5"}, String.class);
//		Assert.assertEquals(1, list.size());
//		
//		SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from test");
//		Assert.assertEquals(1, rs.getRow());
//	}
//	
////	@Test
//	public void testCallableStatementCreator1() {
//		final String callFunctionSql = "{?= call function_test(?)}";
//		List<SqlParameter> params = new ArrayList<SqlParameter>();
//		params.add(new SqlOutParameter("result", Types.INTEGER));
//		params.add(new SqlParameter("str", Types.VARCHAR));
//		
//		
//		Map<String, Object> outValues = jdbcTemplate.call(new CallableStatementCreator() {
//			
//			@Override
//			public CallableStatement createCallableStatement(Connection con)
//					throws SQLException {
//					CallableStatement cstmt = con.prepareCall(callFunctionSql);
//					cstmt.registerOutParameter(1, Types.INTEGER);
//					cstmt.setString(2, "test");
//				return cstmt;
//			}
//		}, params);
//		
//		Assert.assertEquals(4, outValues.get("result"));
//	}
//	
////	@Test
//	public void testNamedParameterJdbcTemplate1() {
//		NamedParameterJdbcTemplate namedParameterJdbcTemplate =
//				new NamedParameterJdbcTemplate(jdbcTemplate);
//		String insertSql = " insert into test(name) values(:name) ";
//		String selectSql = " select * from test where name = :name ";
//		String deleteSql = " delete from test where name = :name ";
//		Map<String, Object> paramMap = new HashMap<String, Object>();
//		paramMap.put("name", "name5");
//		
//		namedParameterJdbcTemplate.update(insertSql, paramMap);
//		
//		final List<Integer> result = new ArrayList<Integer>();
//		namedParameterJdbcTemplate.query(selectSql, paramMap, new RowCallbackHandler() {
//			
//			@Override
//			public void processRow(ResultSet rs) throws SQLException {
//				result.add(rs.getInt("id"));
//			}
//		});
//		Assert.assertEquals(1, result.size());
//		
//		SqlParameterSource paramSource = new MapSqlParameterSource(paramMap);
//		namedParameterJdbcTemplate.update(deleteSql, paramSource);
//	}
//	
////	@Test
//	public void testNamedParameterJdbcTemplate2() {
//		NamedParameterJdbcTemplate namedParameterJdbcTemplate =
//				new NamedParameterJdbcTemplate(jdbcTemplate);
//		UserModel userModel = new UserModel();
//		userModel.setMyName("name5");
//		String insertSql = " insert into test(name) values(:myName) ";
//		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(userModel);
//		namedParameterJdbcTemplate.update(insertSql, paramSource);
//	}
//	
////	@Test
//	public void testSimpleJdbcTemplate() {
//		SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(jdbcTemplate);
//		String insertSql = " insert into test(name) values(?) ";
//		simpleJdbcTemplate.update(insertSql, "name5");
//		String selectSql = " select * from test where name = ? ";
//		List<Map<String, Object>> result = simpleJdbcTemplate.queryForList(selectSql, "name5");
//		Assert.assertEquals(1, result.size());
//		RowMapper<UserModel> mapper = new UserRowMapper();
//		List<UserModel> result2 = simpleJdbcTemplate.query(selectSql, mapper, "name5");
//		Assert.assertEquals(1, result2.size());
//	}
//	
//	@Test
//	public void testCallableStatementCreator3() {
//		final String callProcedureSql = "{call procedure_test(?, ?)}";
//		List<SqlParameter> params = new ArrayList<SqlParameter>();
//		params.add(new SqlInOutParameter("inoutname", Types.VARCHAR));
//		params.add(new SqlOutParameter("outid", Types.INTEGER));
//		Map<String, Object> outvalues = jdbcTemplate.call(new CallableStatementCreator() {
//			
//			@Override
//			public CallableStatement createCallableStatement(Connection con)
//					throws SQLException {
//				CallableStatement cstmt = con.prepareCall(callProcedureSql);
//				cstmt.registerOutParameter(1, Types.VARCHAR);
//				cstmt.registerOutParameter(2, Types.INTEGER);
//				cstmt.setString(1, "test");
//				return cstmt;
//			}
//		}, params);
//		
//		Assert.assertEquals("hello,test", outvalues.get("inoutname"));
//		Assert.assertEquals(1, outvalues.get("outid"));
//	}
//}
